package org.zjvis.datascience.common.gis.GisUtils;

import com.alibaba.fastjson.JSONObject;
import com.google.common.base.Joiner;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang3.StringUtils;
import org.zjvis.datascience.common.dto.gis.SliceDTO;
import org.zjvis.datascience.common.util.SqlUtil;

public class GisUtil {

//---------------------------------------------------P0需求--------------------------------------------------------------

    public static final String TABS_DEFAULT_NAME = "新画布";
    public static final String NEAR_LAYER_DEFAULT_NAME = "近邻查询";
    public static final String LAYERS_DEFAULT_NAME = "新图层";
    public static final String GIS_FOLDER_NAME = "我的GIS文件";
    public static final Map GIS_TYPE_DIC;
    public static final String DATASET_SCHEME = "dataset.";
    public static final String VIEW_SCHEME = "pipeline";
    public static final String REGEX = ".*[`~!-$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？\\\\]+.*";
    public static final int STANDARD_SRID = 4326; //地球坐标系，国际使用的坐标系，天地图用

    static {
        Map gisMap = new HashMap();
        gisMap.put("MULTILINESTRING", "line");
        gisMap.put("MULTIPOLYGON", "fill");
        gisMap.put("MULTIPOINT", "circle");
        gisMap.put("LINESTRING", "line");
        gisMap.put("POLYGON", "fill");
        gisMap.put("POINT", "circle");
        GIS_TYPE_DIC = Collections.unmodifiableMap(gisMap);
    }


    public static JSONObject getTabDataJson() {
        JSONObject tabDataJson = new JSONObject();
        tabDataJson.put("scale", true);
        tabDataJson.put("lnglat", true);
        tabDataJson.put("legend", false);
        tabDataJson.put("mapType", 1);
        tabDataJson.put("layerType", 1);
        return tabDataJson;
    }

    public static int calConverter(String unit) {
        int rate;
        switch (unit) {
            case "km":
                rate = 1000;
                return rate;
            case "m":
            case "m2":
                rate = 1;
                return rate;
            case "km2":
                rate = 1000000;
                return rate;
            default:
                throw new IllegalStateException("Unexpected value: " + unit);
        }
    }

    /**
     * @param result
     * @param unit
     * @return 返回转换后的结果
     */
    public static Float unitConverter(Float result, String unit) {
        int rate;
        switch (unit) {
            case "km":
                rate = 1000;
                return result / rate;
            case "m":
            case "m2":
                rate = 1;
                return result / rate;
            case "km2":
                rate = 1000000;
                return result / rate;
        }
        return result;
    }

    /**
     * @param result
     * @param unit
     * @return buffer单位返回转换后的结果
     */
    public static double bufferUnitConverter(double result, String unit) {
        int rate;
        switch (unit) {
            case "km":
                rate = 1000;
                return result * rate;
            case "m":
                rate = 1;
                return result * rate;
        }
        return result;
    }

    public static String renameIdColSql(String viewName) {
        String sql = "alter table " + viewName + " rename id TO rid";
        return sql;
    }

    public static String geomToGeojsonSql(String tbName) {
        String geojsonSql = "with a as(select t.*, st_asgeojson(geom_4326)::json as geometry, t._record_id_ as record_id from " + tbName + " t)," +
                "feature as(select 'Feature' as type, geometry, record_id, (select row_to_json(fields) from (select a.*) as fields) as properties from a)," +
                "features as(select 'FeatureCollection' as type,array_to_json(array_agg(feature.* order by record_id)) as features from feature)" +
                "select row_to_json(features.*) from features;";
        return geojsonSql;
    }

    public static String downloadGisDataSql(String tbName, List<String> columnNames) {
        columnNames = SqlUtil.formatPGSqlCols(columnNames);
        String sql = "SELECT " +
                "%s " +
                "FROM %s;";
        return String.format(sql, String.join(",", columnNames),  tbName);
    }

    public static String geomToGeoJsonSql(String tbName, List<String> columnNames, long offset, long pageSize) {
        columnNames = SqlUtil.formatPGSqlCols(columnNames);
        String sql = "SELECT " +
                    "%s, st_asgeojson(geom_4326) as geo_json " +
                    "FROM %s " +
                    "WHERE _record_id_ > %s " +
                    "ORDER by _record_id_ " +
                    "LIMIT %s;";
        return String.format(sql, String.join(",", columnNames),  tbName, offset, pageSize);
    }

    // 对于第一次创建时query geom的type
    public static String geomTypeSql(String tbName) {
        String geomTypeSql = "select GeometryType(t.geom) from " + tbName + " t;";
        return geomTypeSql;
    }

    // 基于老图层创建新geom_4326 query图层的type
    public static String newGeomTypeSql(String tbName) {
        String geomTypeSql = "select GeometryType(t.geom_4326) from " + tbName + " t;";
        return geomTypeSql;
    }

//    //    不转换直接创建view的sql
//    public static List<String> createViewSql(String tbName, Long projectId, Long tabId, Long layerId) {
//        Long timeStamp = System.currentTimeMillis();
//        String viewName = "pipeline.gis_view_" + projectId + "_" + tabId + "_" + layerId + "_" + timeStamp;
//        String createViewSql = "create view " + viewName + " as select * from " + tbName + ";";
//        List<String> viewData = new ArrayList<>();
//        viewData.add(viewName);
//        viewData.add(createViewSql);
//        return viewData;
//    }

    public static String addGeom4326ColumnSql(String tbName, String geomType) {
        String datasetSchema = tbName.split("\\.")[0];
        String datasetTableName = tbName.split("\\.")[1];
        return String.format("SELECT AddGeometryColumn('%s','%s','geom_4326', '4326', '%s',2);",
                datasetSchema, datasetTableName, geomType);
    }

    public static String updateGeom4326ColumnSql(String tbName,  String srid, String proj4Text, long offset, long limit) {
        if (srid.equals(String.valueOf(STANDARD_SRID)) || StringUtils.isBlank(proj4Text)) {
            return String.format(
                    "update %s "
                    + "set geom_4326 = ST_SetSRID(geoc_wgs84togcj02(ST_SetSRID(geom, 4326)), 4326) "
                    + "where _record_id_ between %s and %s;", tbName, offset+1, offset+limit);
        } else {
            return String.format(
                    "update %s "
                    + "set geom_4326 = ST_SetSRID(geoc_wgs84togcj02(ST_Transform(geom, '%s', 4326)), 4326) "
                    + "where _record_id_ between %s and %s; ", tbName, proj4Text, offset+1, offset+limit);
        }
    }

    // 根据原表转换成SRID的view的sql
    public static List<String> createViewSql(String tbName, Long projectId, Long tabId, Long layerId) {
        Long timeStamp = System.currentTimeMillis();
        String viewName = "pipeline.gis_view_" + projectId + "_" + tabId + "_" + layerId + "_" + timeStamp;
        String createViewSql = String.format("create view %s as select * from %s;", viewName, tbName);
        List<String> viewData = new ArrayList<>();
        viewData.add(viewName);
        viewData.add(createViewSql);
        return viewData;
    }

    // 根据原表转换成4326的view的sql
    public static List<String> createViewSql4326(String srid, String tbName, Long projectId, Long tabId, Long layerId) {
        Long timeStamp = System.currentTimeMillis();
        String viewName = "pipeline.gis_view_" + projectId + "_" + tabId + "_" + layerId + "_" + timeStamp;
        String createViewSql = "create view " + viewName + " as select *, ST_Transform(st_geomfromtext(ST_AsText(geom), " + srid + "), 4326) as geom_4326 from " + tbName + ";";
        List<String> viewData = new ArrayList<>();
        viewData.add(viewName);
        viewData.add(createViewSql);
        return viewData;
    }

    public static String createFromCoordinateSql(String sourceTable, String targetTable, List<String> cols, String lonCol, String latCol) {
        String newCol = String.format("st_point(%s, %s) as geom", lonCol, latCol);
        cols.add(newCol);
        String sql = String.format("create table %s as select %s from %s", targetTable, Joiner.on(",").join(cols), sourceTable);
        return sql;
    }

    public static String pointToLineSql(String col, String orderCol, String source, String target){
        return String.format("create view %s as select row_number() over() as _record_id_, %s, ST_MakeLine(geom_4326 order by %s) "
            + "as geom_4326 from %s group by %s", target, col, orderCol, source, col);
    }

    public static String pointToLineSql(String orderCol, String source, String target){
        return String.format("create view %s as select row_number() over() as _record_id_, ST_MakeLine(geom_4326 order by %s) "
                + "as geom_4326 from %s", target, orderCol, source);
    }

    public static String getSRIDSql(String sourceTbName) {
        String sql = "select srid from geometry_columns where f_table_name = '" + sourceTbName + "';";
        return sql;
    }

    public static String getTbColumnName(String tableName) {
        String datasetSchema = tableName.split("\\.")[0];
        String tbName = tableName.split("\\.")[1];
        return String.format("select "
                + "column_name "
                + "from INFORMATION_SCHEMA.Columns "
                + "where table_schema = '%s' "
                + "and table_name = '%s';", datasetSchema, tbName);
    }


    public static String getTbColWithoutGeom(String sourceTbName) {
        String tbName = sourceTbName.replaceAll(DATASET_SCHEME, "");
        String getTbColWithoutGeomSql = "select column_name from INFORMATION_SCHEMA.Columns where table_name = '" + tbName + "' and column_name != 'geom';";
        return getTbColWithoutGeomSql;
    }


    public static List<String> calAreaSql(String oldView, Long projectId, Long tabId, Long layerId, String columnName, String unit, Integer precision) {
        Long timeStamp = System.currentTimeMillis();
        int rate = calConverter(unit);
        //create new view name based on current timestamp
        String viewName = "pipeline.gis_view_" + projectId + "_" + tabId + "_" + layerId + "_" + timeStamp;
        //Add new view
        String calAreaSql = "CREATE VIEW " + viewName +
                " AS SELECT t.*, Round((st_area(Geography(t.geom_4326))/" + rate + ")::numeric, " + precision + ") as " + columnName + " from " + oldView + " t;";
        List<String> viewData = new ArrayList<>();
        viewData.add(viewName);
        viewData.add(calAreaSql);
        return viewData;
    }

    public static List<String> calPerimeterSql(String oldView, Long projectId, Long tabId, Long layerId, String columnName, String unit, Integer precision) {
        Long timeStamp = System.currentTimeMillis();
        int rate = calConverter(unit);
        //create new view name based on current timestamp
        String viewName = "pipeline.gis_view_" + projectId + "_" + tabId + "_" + layerId + "_" + timeStamp;
        //Add new view
        String calAreaSql = "CREATE VIEW " + viewName +
                " AS SELECT t.*, Round((st_perimeter(Geography(t.geom_4326))/" + rate + ")::numeric, " + precision + ") as " + columnName + " from " + oldView + " t;";
        List<String> viewData = new ArrayList<>();
        viewData.add(viewName);
        viewData.add(calAreaSql);
        return viewData;
    }

    public static List<String> calLengthSql(String oldView, Long projectId, Long tabId, Long layerId, String columnName, String unit, Integer precision) {
        Long timeStamp = System.currentTimeMillis();
        int rate = calConverter(unit);
        //create new view name based on current timestamp
        String viewName = "pipeline.gis_view_" + projectId + "_" + tabId + "_" + layerId + "_" + timeStamp;
        //Add new view
        String calAreaSql = "CREATE VIEW " + viewName +
                " AS SELECT t.*, Round((st_length(Geography(t.geom_4326))/" + rate + ")::numeric," + precision + ") as " + columnName + " from " + oldView + " t;";

        List<String> viewData = new ArrayList<>();
        viewData.add(viewName);
        viewData.add(calAreaSql);
        return viewData;
    }

    public static List<String> calCentroidSql(String oldView, Long projectId, Long tabId, Long layerId, String cols) {
        Long timeStamp = System.currentTimeMillis();
        //create new view name based on current timestamp
        String viewName = "pipeline.gis_view_" + projectId + "_" + tabId + "_" + layerId + "_" + timeStamp + "_centorid";
        //Add new view
        String calCentroidSql = "CREATE VIEW " + viewName +
                " AS SELECT t._record_id_, ST_Centroid(t.geom_4326) as geom_4326 from " + oldView + " t;";
        List<String> viewData = new ArrayList<>();
        viewData.add(viewName);
        viewData.add(calCentroidSql);
        return viewData;
    }

    public static List<String> calBufferSql(String oldView, Long projectId, Long tabId, Long layerId, String cols, double radius) {
        Long timeStamp = System.currentTimeMillis();
        //create new view name based on current timestamp
        String viewName = "pipeline.gis_view_" + projectId + "_" + tabId + "_" + layerId + "_" + timeStamp + "_buffer";
//        String calBufferSql = "CREATE VIEW " + viewName +
//                " AS SELECT " + cols + ", st_buffer(geom, " + radius + ") as geom from " + oldView + ";";
        String calBufferSql = "CREATE VIEW " + viewName +
                " AS SELECT t._record_id_, st_buffer(t.geom_4326 :: geography, " + radius + ") as geom_4326 from " + oldView + " t;";
        List<String> viewData = new ArrayList<>();
        viewData.add(viewName);
        viewData.add(calBufferSql);
        return viewData;
    }


    public static String addAllSql(String viewName, String columnName) {
        String addAllSql = "select sum(t." + columnName + ") from " + viewName + " t;";
        return addAllSql;
    }

    public static String queryViewCol(String viewName) {
        String setViewName = viewName.replaceAll("pipeline.", "");
        String queryViewColSql = "select a.attname " +
                "from pg_catalog.pg_attribute a,pg_catalog.pg_class c, pg_catalog.pg_namespace n " +
                "where a.attrelid=c.oid and c.relname='" + setViewName + "' and a.attnum>0 " +
                "AND NOT a.attisdropped and n.oid = c.relnamespace and n.nspname='" + VIEW_SCHEME + "' " +
                "order by a.attnum;";
        return queryViewColSql;
    }

    public static String getDataType(String viewName) {
        String setViewName = viewName.replaceAll("pipeline.", "");
        String getDataTypeSql = "select column_name, data_type from INFORMATION_SCHEMA.Columns where table_name = '" + setViewName +
                "';";
        return getDataTypeSql;
    }

    public static String getNumericalStat(String col, String filterSql, String viewName, Integer precision) {
        return String.format("select Round(min(%s)::numeric, %s) as min, Round(max(%s)::numeric, %s) as max, Round((max(%s)-min(%s))::numeric, %s) as diff, "
            + "Round(avg(%s)::numeric, %s) as avg from %s%s", col, precision, col, precision, col, col, precision, col, precision, viewName, filterSql);
    }

    public static String getDateStat(String col, String standardCol, String filterSql, String viewName, float factor, Integer precision) {
        return String.format("select min(%s), max(%s), "
            + "Round(EXTRACT(EPOCH FROM (max(%s)::timestamp-min(%s)::timestamp))::numeric/%s, %s) as diff from %s%s",
            col, col, standardCol, standardCol, factor, precision, viewName, filterSql);
    }

    public static String getDistinctCount(String viewName, String filterSql) {
        return String.format("select distinct count(1) from %s%s", viewName, filterSql);
    }

    //---------------------------------------------------P1需求--------------------------------------------------------------
    public static String getNearElementsId(String currentViewName, String targetViewName, Long elementId, Integer neighbors) {
        String getNearElementsIdSql = "select d._record_id_ from " + currentViewName + " t," + targetViewName + " d" +
                " where t._record_id_ = " + elementId + " order by ST_Distance(t.geom_4326, d.geom_4326) ASC " +
                "LIMIT " + neighbors + ";";
        return getNearElementsIdSql;
    }

    public static List<String> createLayerByNearIds(Long projectId, Long tabId, Long layerId, String targetViewName, String Ids) {
        Long timeStamp = System.currentTimeMillis();
        String newViewName = "pipeline.gis_view_" + projectId + "_" + tabId + "_" + layerId + "_" + timeStamp + "_ns";
        String createNearViewSql = "create view " + newViewName + " as select * from " + targetViewName + " t where t._record_id_ in (" + Ids + ");";
        List<String> viewData = new ArrayList<>();
        viewData.add(newViewName);
        viewData.add(createNearViewSql);
        return viewData;
    }

    public static String countElements(String targetViewName) {
        String sql = "select count(*) from " + targetViewName;
        return sql;
    }

    public static String constructGeoJsonCacheKey(String viewName, SliceDTO sliceDTO) {
        List<String> strList = new ArrayList<>();
        strList.add(viewName.split("\\.")[0]);
        strList.add(viewName.split("\\.")[1]);
        strList.add(String.valueOf(sliceDTO.getOffset()));
        strList.add(String.valueOf(sliceDTO.getPageSize()));
        return StringUtils.join(strList, "_");
    }

    public static String constructGisSliceCacheKey(String tbName, Long sliceBytes, Long maxSliceNumbers) {
        List<String> strList = new ArrayList<>();
        strList.add(tbName.split("\\.")[0]);
        strList.add(tbName.split("\\.")[1]);
        strList.add(String.valueOf(sliceBytes));
        strList.add(String.valueOf(maxSliceNumbers));
        return StringUtils.join(strList, "_");
    }

}
